package com.swf.seed.xls.sheet;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.swf.seed.triutil.lang.SdFileUtils;

/**
 * description:poi处理文件的相关方法
 * 
 * @version 1.0 2013-9-9
 * @author shenwf
 */
public class FwspoiUtils {
	public static Logger log = LoggerFactory.getLogger(FwspoiUtils.class);
	private static int rowBegin = 0;
	private static int rowEnd = Integer.MAX_VALUE;
	private static int colBegin = 0;
	private static int colEnd = Integer.MAX_VALUE;
	
	/**
	 * 加载excel文件
	 * 
	 * @param fileName
	 * @throws IOException
	 */
	public static Workbook readExcel(String fileName) throws IOException {
		InputStream inputStream = new FileInputStream(fileName);
		Workbook wb = null;
		String fileSuffix = SdFileUtils.getFileSuffix(fileName);
		if (fileSuffix.equalsIgnoreCase("xls")) {
			wb = new HSSFWorkbook(inputStream);
		} else if (fileSuffix.equalsIgnoreCase("xlsx")) {
			wb = new XSSFWorkbook(inputStream);
		} else {
			log.error("unvalid file type");
		}
		return wb;
	}
	
	public static Cell[][] getCells(Sheet sheet){
		int totalRowNum = sheet.getLastRowNum();
		
		if (totalRowNum<rowBegin){
			log.error("rowbeign number["+rowBegin+"] should shouldn't larger totalRowNum["+totalRowNum+"]");
		}
		if (rowBegin<0){
			rowBegin = 0;
		}
		if (rowEnd>totalRowNum){
			rowEnd = totalRowNum;
		}
		int maxColNum = getMaxColumn(sheet);
		if (colBegin > maxColNum ){
			log.error("colbeign number["+colBegin+"] should shouldn't larger totalColumnNum["+maxColNum+"]");
		}
		if (colBegin < 0){
			colBegin = 0;
		}
		if (colEnd > maxColNum){
			colEnd = maxColNum; 
		}
		Cell[][] cells = new Cell[rowEnd-rowBegin+1][colEnd-colBegin+1];
		for (int i=rowBegin;i<=rowEnd;i++){
			Row row = sheet.getRow(i);
			for (int j=colBegin;j<=colEnd;j++){
				cells[i][j] = row.getCell(j);
			}
		}
		return cells;
		
	}
	public static String getStringCellValue(Cell cell) {
		String returnValue = "";
		if (null != cell) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC: // 数字
				Double doubleValue = cell.getNumericCellValue();
				String str = doubleValue.toString();
				if (str.contains(".0")) {
					str = str.replace(".0", "");
				}
				Integer intValue = Integer.parseInt(str);
				returnValue = intValue.toString();
				break;
			case Cell.CELL_TYPE_STRING: // 字符串
				returnValue = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN: // 布尔
				Boolean booleanValue = cell.getBooleanCellValue();
				returnValue = booleanValue.toString();
				break;
			case Cell.CELL_TYPE_BLANK: // 空值
				returnValue = "";
				break;
			case Cell.CELL_TYPE_FORMULA: // 公式
				returnValue = cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_ERROR: // 故障
				returnValue = "";
				break;
			default:
				log.warn("unknowed type");
				break;
			}
		}
		return returnValue;

	}

	
	public static int getMaxColumn(Sheet sheet){
		int maxColNum = Integer.MIN_VALUE;
		for (Row row:sheet){
			int lastCellNum = row.getLastCellNum()-1;
			if (lastCellNum>maxColNum){
				maxColNum = lastCellNum ;
			}
		}
		return maxColNum;
	}
}
